package com.edu.app.mapper.core;

import com.baomidou.mybatisplus.annotation.SqlParser;
import com.edu.app.bean.core.SysCustomer;
import com.edu.app.bean.virtualbean.ObsoleteCustomer;
import com.edu.app.util.AppBaseMapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface SysCustomerMapper extends AppBaseMapper<SysCustomer> {
    /*@Select("SELECT * FROM S_CUSTOMER WHERE status = 2 order by name")
    List<SysCustomer> getObsoleteCustomer();*/
    @Select("insert ignore into s_customer(create_by, create_time, update_by, update_time, name, phone, sex, birthday, status, quality_tag, channel_tag) values ${data}")
    Integer importCustomers(@Param("data") String data);

    @Select("SELECT * FROM S_CUSTOMER WHERE status = 1 order by name")
    List<SysCustomer> getCustomer();

    @Select("SELECT * FROM S_CUSTOMER WHERE status = 0 order by create_time")
    List<SysCustomer> getPoCustomer();

    @Select("SELECT * FROM S_CUSTOMER t3 WHERE status = 0 and DATE(t3.create_time) = DATE(Now()) order by create_time")
    List<SysCustomer> getPoCustomerToday();

    @Select("select distinct t1.* from s_customer t1 inner join s_user_customer t2 on t1.id = t2.customer_id where t1.status = 0")
    List<SysCustomer> getPoCustomerAssigned();

    @Select("select * from s_customer where id not in (select distinct t1.id from s_customer t1 inner join s_user_customer t2 on t1.id = t2.customer_id where t1.status = 0) and status = 0")
    List<SysCustomer> getPoCustomerNotAssigned();

    @SqlParser(filter=true)
    @Select("SELECT DISTINCT * FROM S_CUSTOMER WHERE tenant_id = #{tenant_id} and (nickname like \"%\"#{name}\"%\" or name like \"%\"#{name}\"%\" or REPLACE(phone, ' ','') like \"%\"#{name}\"%\")")
    List<SysCustomer> searchCustomer(@Param("name") String name, @Param("tenant_id") Integer tenant_id);

    @Select("UPDATE S_CUSTOMER SET STATUS=2 WHERE id=#{id}")
    Integer obsoleteCustomer(Integer id);

    @Select("SELECT DISTINCT T1.ID,T1.NAME,T2.NICKNAME AS EMPLOYEE,T1.UPDATE_TIME," +
            "T1.CHANNEL_TAG,T1.OBSOLETE_TAG FROM S_CUSTOMER T1 " +
            "LEFT JOIN S_USER T2 ON t2.username = T1.update_by " +
            "WHERE T1.STATUS=2 ORDER BY ${order} ${seq}")
    List<ObsoleteCustomer> getObsoleteCustomers(@Param("order") String order, @Param("seq") String seq);

    @Select("select * from s_customer where channel_tag=#{channel_tag} " +
            "and id not in (select distinct t1.id from s_customer t1 inner join s_user_customer t2 on t1.id = t2.customer_id) " +
            "and status in (0,8) limit ${number}")
    List<SysCustomer> getCustomerByChannelTag(@Param("channel_tag") String channel_tag, @Param("number") Integer number);

    @Select("select count(*) from s_customer where channel_tag=#{channel_tag} and id not in (select distinct t1.id from s_customer t1 inner join s_user_customer t2 on t1.id = t2.customer_id) and status in (0,8)")
    Integer getCustomerNoByChannelTag(@Param("channel_tag") String channel_tag);
}
